﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Runda.EShop.Common.Entity;
using System.Data.Common;
using System.Data;

namespace Runda.EShop.Dal
{
    public partial class RelationCustomerProductDal
    {
        public DataTable GetProductListByCustomer(Guid CustomerID, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sql = @"select P.* from Bas_Product P WHERE ProductID IN (SELECT ProductID FROM Relation_Customer_Product WHERE CustomerID=@CustomerID)";
            StringBuilder sqlSb = new StringBuilder(sql);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sql, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@CustomerID", DbType.Guid, CustomerID);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable GetProductListByCustomer(Guid CustomerID,string WhereSql, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sql = @"select P.* from Bas_Product P WHERE ProductID IN (SELECT ProductID FROM Relation_Customer_Product WHERE CustomerID=@CustomerID) "+WhereSql;
            StringBuilder sqlSb = new StringBuilder(sql);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sql, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@CustomerID", DbType.Guid, CustomerID);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public void InsertOrUpdate2(RelationCustomerProductEntity entity)
        {
            string sql = @"
            IF NOT EXISTS (SELECT 1 FROM Relation_Customer_Product WHERE CustomerID=@CustomerID AND ProductID=@ProductID)
BEGIN
INSERT INTO Relation_Customer_Product(ID,CustomerID,ProductID,Deleted) VALUES (@ID,@CustomerID,@ProductID,@Deleted)
END
            ";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);

            CurrentDatabase.AddInParameter(dbcommand, "@ID", DbType.Guid, entity.ID);
            CurrentDatabase.AddInParameter(dbcommand, "@CustomerID", DbType.Guid, entity.CustomerID);
            CurrentDatabase.AddInParameter(dbcommand, "@ProductID", DbType.Guid, entity.ProductID);
            CurrentDatabase.AddInParameter(dbcommand, "@Deleted", DbType.Boolean, entity.Deleted);

            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        public void Delete(Guid CustomerID,Guid ProductID)
        {
            string sql = @"DELETE FROM Relation_Customer_Product WHERE CustomerID=@CustomerID and ProductID=@ProductID";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
             
            CurrentDatabase.AddInParameter(dbcommand, "@CustomerID", DbType.Guid, CustomerID);
            CurrentDatabase.AddInParameter(dbcommand, "@ProductID", DbType.Guid, ProductID); 

            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }
    }
}
